import sqlite3
import numpy as np
import os
from functools import cmp_to_key

def compare_mtime(x, y):
    stat_x = os.stat(x)
    stat_y = os.stat(y)
    if stat_x.st_mtime < stat_y.st_mtime:
        return -1
    elif stat_x.st_mtime > stat_y.st_mtime:
        return 1
    else:
        return 0
    
class readDB:
    def readColInfo(self, path):
        conn = sqlite3.connect(path)
        cursor = conn.cursor()
        cursor.execute('PRAGMA table_info(Radar_0)')
        colInfo = list(cursor.fetchall())
        cursor.close()
        conn.close()
        return colInfo

    def readFrmNum(self, path):
        conn = sqlite3.connect(path)
        cursor = conn.cursor()
        cursor.execute('select max(rowid) from Radar_0')
        frmNum = cursor.fetchone()[0]
        cursor.close()
        conn.close()
        return frmNum

    def readOneHydRow(self, path, frm):
        conn = sqlite3.connect(path)
        cursor = conn.cursor()
        cursor.execute(f"select * from Radar_0 where rowid = {frm+1}")
        databuffer = cursor.fetchone()
        cursor.close()
        conn.close()
        return databuffer

    def readOneHydRaw(self, path, frm, col):
        conn = sqlite3.connect(path)
        cursor = conn.cursor()
        cursor.execute("select Captime,%s from Radar_0 where rowid = %d"%(col, frm+1))
        databuffer = cursor.fetchone()
        cursor.close()
        conn.close()
        invaild = 0
        if databuffer[1] is invaild:
            return [None, None]
        frmStamp = databuffer[0]
        frmData = np.frombuffer(databuffer[1], dtype=np.int8)
        return frmStamp, frmData

class RadarDB(readDB):
    def __init__(self, db="hyd"):
        self.dbSuffix = db
        self.fileList = []
        self.fileFrm = []
        self.colInfo = []
        
    def setFileCd(self, cd):
        dirs = os.listdir(cd)
        fileList = [os.path.join(cd, file) for file in dirs]
        return self.setFileList(fileList)

    def setFileList(self, fileList:list, sort=True):
        '''
        filter exist file by db suffix and sort the database file by modify time
        fileList: list of database file
        return: total frame number
        Notice: fileList will be sorted by modify time, this may not capture time order
        '''
        if not isinstance(fileList, list): fileList = [fileList]
        self._resetRecoder()
        dbFileList = [os.path.abspath(file) for file in fileList if file.split(".")[-1]==self.dbSuffix]
        for file in dbFileList:
            if file.split(".")[-1]!=self.dbSuffix: continue
            if os.path.isfile(file):
                self.fileList.append(file)
            else:
                print("file path: %s is not found"%file)
        if len(self.fileList)==0:
            print("No Vaild files founded, please check fileList or Cd")
            return -1
        if sort==True:
            self._sortFileList()
        self.fileFrm.append(0)
        for file in self.fileList:
            self.frmTotal += self.readFrmNum(file)
            self.fileFrm.append(self.frmTotal)
        self.colInfo = self.readColInfo(file)
        return self.frmTotal

    def readOneRaw(self, frm:int, col="*"):
        if frm>=self.frmTotal:
            print("invaild frame")
            return
        for fileIdx, frmPtr in enumerate(self.fileFrm):
            if frm<frmPtr: break
        file = self.fileList[fileIdx-1]
        fileFrm = frm - self.fileFrm[fileIdx-1]
        if self.dbSuffix == "hyd":
            if col == "*":
                data = self.readOneHydRow(file, fileFrm)
                if data is None:
                    return [None, None]
                else:
                    col = self.getColName()
                    frmStamp = data[col.index("Captime")]
                    frmData = {}
                    for idx, name in enumerate(col):
                        if data[idx] != b"null":
                            frmData[name] = data[idx]
                    return [frmStamp, frmData]
            else:
                return self.readOneHydRaw(file, fileFrm, col)
        
    def getColName(self):
        return [i[1] for i in self.colInfo]
    
    def _sortFileList(self, by="mtime"):
        if by=="mtime":
            self.fileList = sorted(self.fileList, key=cmp_to_key(compare_mtime))
        else:
            pass

    def _resetRecoder(self):
        self.fileList = []
        self.fileFrm = []
        self.frmTotal = 0
    
    def getFrmTotal(self): return self.frmTotal
    def getFileList(self): return self.fileList


if __name__ == "__main__":
    db = RadarDB()
    db.setFileList(r"d:\CapData\NewRock\2025_01_09_15_51_16.hyd")
    print(db.getFrmTotal())
    print(db.getColName())
    print(len(db.readOneRaw(2)))